package crivia.db.a;

public class Specially_MySQL extends Specially_Abstract {
	
	@Override
	public String toInt(String tSQL) {
		return "cast(" + tSQL + " as SIGNED)";
	}

	/**> <b>cast(</b>?<b> as varchar)</b>*/
	@Override
	public String toChar(String tSQL) {
		return "cast(" + tSQL + " as varchar(4000))";
	}

	/**> <b>str_to_date(</b>?<b> , '%Y-%m-%d')</b>*/
	@Override
	public String toDate(String tSQL) {
		return "str_to_date(" + tSQL + ",'%Y-%m-%d')";
	}

	/**> <b>str_to_date(</b>?<b> , '%Y-%m-%d %H:%i:%s')</b>*/
	@Override
	public String toDateTime(String tSQL) {
		return "str_to_date(" + tSQL + ",'%Y-%m-%d %H:%i:%s')";
	}

	/**> <b>now()</b>*/
	@Override
	public String today() {
		return "now()";
	}

	/**> <b>unix_timestamp(</b>?<b>)</b>*/
	@Override
	public String toTimestamp(String tSQL, int n) {
		return "unix_timestamp(" + tSQL + ")";
	}

	/**> <b>ifnull(</b>?<b>, </b>?<b>)</b>*/
	@Override
	public String isNull(String tSQL, String tSQL2) {
		return "ifnull("+tSQL+","+tSQL2+")";
	}

	/**> <b>(</b>?<b> = '' or </b>?<b> is null)</b>*/
	@Override
	public String isNull(String tSQL) {
		return "(" + tSQL + " = '' or "+tSQL + " is null)";
	}

	/**> <b>(</b>?<b> != '' or </b>?<b> is not null)</b>*/
	@Override
	public String isNotNull(String tSQL) {
		return "(" + tSQL + " != '' and "+tSQL + " is not null)";
	}

	/**> <b></b>?<b></b>*/
	@Override
	public String keyWordString(String tSQL) {
		return tSQL;
	}

	@Override
	public String hasTable(String tableName) {
		return "select 1 from information_schema.TABLES  where table_name = '"+tableName+"' ";
	}

	/**> ?<b> + </b>?*/
	@Override
	public String concatString(String... strings) {
		if (null == strings || 0 == strings.length){
			return null;
		}
		if (1 == strings.length){
			return strings[0];
		}
		StringBuilder s = new StringBuilder(strings[0]);
		for (int k = 1; k < strings.length; k++){
			s.append("+").append(toChar(strings[k]));
		}
		return s.toString();
	}

	@Override
	public String splitPage(String fields, String namedFields, String pk
			, String table, String cd
			, String groupBy, String orderBy
			, int pi, int ps) {
		boolean hasOrderBy = true
				&& null != orderBy 
				&& ! "".equals(orderBy);
		String[] orderBys = hasOrderBy ? orderBy.split(",") : new String[]{};
		StringBuilder sortedOrderBy = new StringBuilder();
		for (String o : orderBys){
			if ("".equals(o.trim())){
				continue;
			}
			String[] kws = o.trim().split(" ");
			sortedOrderBy.append(o);
			if (kws.length == 1){
				sortedOrderBy.append(" asc ");
			}
			sortedOrderBy.append(" , ");
		}
		orderBy = sortedOrderBy.toString();
		StringBuilder s = new StringBuilder("select ").append(fields)
			.append(" from ").append(table).append(" , ( select top ").append(ps)
			.append(" spt1.pk from ( select distinct top ").append(pi * ps)
			.append(" ").append(orderBy.toLowerCase()
				.replaceAll("asc", "").replaceAll("desc", ""))
			.append(" pk")
			.append(" from (select ")
				.append(" ").append(orderBy.toLowerCase()
					.replaceAll("asc", "").replaceAll("desc", ""))
				.append(pk).append(" pk")
				.append(" from ").append(table)
				.append(" where (").append(cd).append(") ")
//				.append((true
//						&& ECR.ns(table).trim().startsWith("(")
//						&& ECR.ns(table).trim().endsWith(")"))
//					? " SplitPageRawTable" : "")
			.append(") SplitPageRawTable").append(" order by ")
			.append(orderBy).append(" pk asc ").append(" ) spt1 order by ")
			.append(orderBy.toLowerCase()
				.replaceAll("asc", "<DESC>").replaceAll("desc", "<ASC>")
				.replaceAll("<DESC>", "desc").replaceAll("<ASC>", "asc"))
			.append("spt1.pk desc ) spt2 where t.").append(pk).append(" = spt2.pk")
			.append(groupBy)
			.append(" order by ").append(orderBy).append(" spt2.pk asc ");
		return s.toString();
	}

	@Override
	public String splitPage(String fields, String pk
			, String table, String cd
			, String orderBy
			, int pi, int ps) {
		return splitPage(fields, fields, pk, table, cd, "", orderBy, pi, ps);
	}

	@Override
	public String splitPage(String fields, String pk
			, String table, String cd
			, int pi, int ps) {
		return splitPage(fields, fields, pk, table, cd, "", "", pi, ps);
	}

	@Override
	public String splitPage(String fields, String pk
			, String table
			, int pi, int ps) {
		return splitPage(fields, fields, pk, table, "", "", "", pi, ps);
	}
	
	
//	public static void main(String[] args) {
//		System.out.println(new Specially_MySQL()
//		.splitPage(" id , lastName ,departmentID ", " id ", " hrmResource "
//				, null, null, 2, 5));
//	}

	@Override
	public String indexOf(String sqlSource, String sqlTarget) {
		return "LOCATE(" + sqlTarget + " , " + sqlSource + ")";
	}

	@Override
	public String subString(String tSQL, String begin, String end) {
		return "substr(" + tSQL + " , " + begin + " , " + end + ")";
	}
	
	@Override
	public String stringLength(String tSQL) {
		return "char_length(" + tSQL + ")";
	}

	@Override
	public String moneyFormatFloat2(String tSQL) {
		return "Format("+tSQL+")";
	}

	

}
